import pandas as pd
import warnings

warnings.filterwarnings("ignore")
pd.set_option("display.max_columns", None)
pd.set_option("display.max_rows", None)
pd.set_option("display.float_format", "{:.2f}".format)

# Load the csv file
real_trips = pd.read_csv("NYC TLC Trip Record.csv")
trips = real_trips.copy()

# Create a dictionary to map column names to sample values
sample_values = dict()
for column in trips.columns:
    sample_values[column] = trips[column].head(1).values[0]
sample_values

# Check formatting inconsistencies
trips_dtypes = trips.dtypes.reset_index().rename(columns={0: "Type", "index": "Column Name"})
trips_dtypes["sample Value"] = trips_dtypes["Column Name"].map(sample_values)
trips_dtypes

# Check for missing values
trips_isnull_sum = trips.isnull().sum().reset_index()
trips_isnull_sum[1] = trips_isnull_sum[0] / len(trips) * 100

columns_to_rename = {0: "Missing Value Counts", 1: "Percentage (%)", "index": "Column Name"}
trips_isnull_sum = trips_isnull_sum.rename(columns=columns_to_rename)
trips_isnull_sum = trips_isnull_sum[trips_isnull_sum["Missing Value Counts"]!=0]
trips_isnull_sum

trips.duplicated().sum()

0

import matplotlib.pyplot as plt
# Check outliers
columns_to_check = [column for column in trips.columns if trips[column].dtype in ["float64", "int64"] and column!="ehail_fee" and "ID" not in column and "type" not in column]

# Create boxplots for each column in separate subplots
num_columns = len(trips.columns)
rows = 2
cols = 5

# Create a figure with specified size
fig, axs = plt.subplots(rows, cols, figsize=(18, 12))

# Flatten the axes array for easy iteration
axs = axs.flatten()

# Create horizontal boxplots
for i, column in enumerate(columns_to_check):
    trips.boxplot(column=column, ax=axs[i])
    axs[i].set_title(column.replace("_", " ").title().replace("Mta", "MTA"))

# Adjust layout
plt.tight_layout()

# Display the plot
plt.show()

# Renaming the columns
columns_to_rename = {
    "VendorID": "vendor_id",
    "lpep_pickup_datetime": "pickup_datetime",
    "lpep_dropoff_datetime": "dropoff_datetime",
    "store_and_fwd_flag": "store_and_fwd_flag",
    "RatecodeID": "rate_code_id",
    "PULocationID": "pickup_location_id",
    "DOLocationID": "dropoff_location_id",
    "passenger_count": "passenger_count",
    "ehail_fee": "ehail_fee",
    "payment_type": "payment_type_id",
    "trip_type": "trip_type_id",
    "congestion_surcharge": "congestion_surcharge"
}

trips = trips.rename(columns=columns_to_rename)
trips.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 68211 entries, 0 to 68210
Data columns (total 20 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   vendor_id              68211 non-null  int64  
 1   pickup_datetime        68211 non-null  object 
 2   dropoff_datetime       68211 non-null  object 
 3   store_and_fwd_flag     63887 non-null  object 
 4   rate_code_id           63887 non-null  float64
 5   pickup_location_id     68211 non-null  int64  
 6   dropoff_location_id    68211 non-null  int64  
 7   passenger_count        63887 non-null  float64
 8   trip_distance          68211 non-null  float64
 9   fare_amount            68211 non-null  float64
 10  extra                  68211 non-null  float64
 11  mta_tax                68211 non-null  float64
 12  tip_amount             68211 non-null  float64
 13  tolls_amount           68211 non-null  float64
 14  ehail_fee              0 non-null      float64
 15  improvement_surcharge  68211 non-null  float64
 16  total_amount           68211 non-null  float64
 17  payment_type_id        63887 non-null  float64
 18  trip_type_id           63877 non-null  float64
 19  congestion_surcharge   63887 non-null  float64
dtypes: float64(14), int64(3), object(3)
memory usage: 10.4+ MB

# Data type conversion
categoricals = ["vendor_id", "store_and_fwd_flag", "rate_code_id", "payment_type_id", "trip_type_id", "pickup_location_id", "dropoff_location_id"]
datetimes = ["pickup_datetime", "dropoff_datetime"]
for column in categoricals:
    trips[column] = trips[column].astype("category")

for column in datetimes:
    trips[column] = pd.to_datetime(trips[column])

trips.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 68211 entries, 0 to 68210
Data columns (total 20 columns):
 #   Column                 Non-Null Count  Dtype         
---  ------                 --------------  -----         
 0   vendor_id              68211 non-null  category      
 1   pickup_datetime        68211 non-null  datetime64[ns]
 2   dropoff_datetime       68211 non-null  datetime64[ns]
 3   store_and_fwd_flag     63887 non-null  category      
 4   rate_code_id           63887 non-null  category      
 5   pickup_location_id     68211 non-null  category      
 6   dropoff_location_id    68211 non-null  category      
 7   passenger_count        63887 non-null  float64       
 8   trip_distance          68211 non-null  float64       
 9   fare_amount            68211 non-null  float64       
 10  extra                  68211 non-null  float64       
 11  mta_tax                68211 non-null  float64       
 12  tip_amount             68211 non-null  float64       
 13  tolls_amount           68211 non-null  float64       
 14  ehail_fee              0 non-null      float64       
 15  improvement_surcharge  68211 non-null  float64       
 16  total_amount           68211 non-null  float64       
 17  payment_type_id        63887 non-null  category      
 18  trip_type_id           63877 non-null  category      
 19  congestion_surcharge   63887 non-null  float64       
dtypes: category(7), datetime64[ns](2), float64(11)
memory usage: 7.4 MB

# Impute missing values

# MCAR
trips = trips.drop(columns=["ehail_fee"])

# MNAR
trips["trip_type_id"] = trips["trip_type_id"].fillna(trips["trip_type_id"].mode()[0])

# MAR
numericals = ["passenger_count", "congestion_surcharge"]
for column in numericals:
    trips[column] = trips[column].fillna(trips[column].median())

# MAR
categoricals = ["store_and_fwd_flag", "rate_code_id", "payment_type_id"]
for column in categoricals:
    trips[column] = trips[column].fillna(trips[column].mode()[0])

trips.isnull().sum()

vendor_id                0
pickup_datetime          0
dropoff_datetime         0
store_and_fwd_flag       0
rate_code_id             0
pickup_location_id       0
dropoff_location_id      0
passenger_count          0
trip_distance            0
fare_amount              0
extra                    0
mta_tax                  0
tip_amount               0
tolls_amount             0
improvement_surcharge    0
total_amount             0
payment_type_id          0
trip_type_id             0
congestion_surcharge     0
dtype: int64

# Correcting some float values into integer
integers = ["rate_code_id", "passenger_count", "payment_type_id", "trip_type_id"]
for column in integers:
    if column != "passenger_count":
        trips[column] = trips[column].astype("int64").astype("category")
trips.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 68211 entries, 0 to 68210
Data columns (total 19 columns):
 #   Column                 Non-Null Count  Dtype         
---  ------                 --------------  -----         
 0   vendor_id              68211 non-null  category      
 1   pickup_datetime        68211 non-null  datetime64[ns]
 2   dropoff_datetime       68211 non-null  datetime64[ns]
 3   store_and_fwd_flag     68211 non-null  category      
 4   rate_code_id           68211 non-null  category      
 5   pickup_location_id     68211 non-null  category      
 6   dropoff_location_id    68211 non-null  category      
 7   passenger_count        68211 non-null  float64       
 8   trip_distance          68211 non-null  float64       
 9   fare_amount            68211 non-null  float64       
 10  extra                  68211 non-null  float64       
 11  mta_tax                68211 non-null  float64       
 12  tip_amount             68211 non-null  float64       
 13  tolls_amount           68211 non-null  float64       
 14  improvement_surcharge  68211 non-null  float64       
 15  total_amount           68211 non-null  float64       
 16  payment_type_id        68211 non-null  category      
 17  trip_type_id           68211 non-null  category      
 18  congestion_surcharge   68211 non-null  float64       
dtypes: category(7), datetime64[ns](2), float64(10)
memory usage: 6.9 MB

# Outlier removal for numerical columns using iqr
upper_bound_exclusions = ["tolls_amount", "improvement_surcharge", "congestion_surcharge"]
all_exclusions = ["passenger_count"]
numericals = [column for column in trips.columns if trips[column].dtype in ["float64", "int64"] and column not in all_exclusions]
for column in numericals:
    q1 = trips[column].quantile(0.25)
    q3 = trips[column].quantile(0.75)
    iqr = q3 - q1
    lower_bound = q1 - 1.5 * iqr
    upper_bound = q3 + 1.5 * iqr
    if column not in upper_bound_exclusions:
        trips = trips[(trips[column] >= lower_bound) & (trips[column] <= upper_bound)]
    else:
        trips = trips[(trips[column] >= lower_bound)]

print(trips.shape)

import matplotlib.pyplot as plt
# Check outliers
columns_to_filter = numericals.copy()
columns_to_filter = columns_to_filter + ["passenger_count"]
# Create boxplots for each column in separate subplots
num_columns = len(trips.columns)
rows = 2
cols = 5

# Create a figure with specified size
fig, axs = plt.subplots(rows, cols, figsize=(18, 12))

# Flatten the axes array for easy iteration
axs = axs.flatten()

# Create horizontal boxplots
for i, column in enumerate(columns_to_filter):
    trips.boxplot(column=column, ax=axs[i])

# Adjust layout
plt.tight_layout()

# Display the plot
plt.show()

(43484, 19)

# Extract hour, day of week, and month from pickup datetime
trips["pickup_hour"] = trips["pickup_datetime"].dt.hour
trips["pickup_day_of_week"] = trips["pickup_datetime"].dt.day_name()
trips["pickup_month"] = trips["pickup_datetime"].dt.month_name()
trips["trip_duration_minutes"] = (trips["dropoff_datetime"] - trips["pickup_datetime"]).dt.total_seconds() / 60
trips["trip_duration_minutes"] = round(trips["trip_duration_minutes"], 2)
trips["trip_duration_hours"] = round(trips["trip_duration_minutes"] / 60, 2)
trips.head()

# Mapping Id to Name based on Data Dictionary
# Create vendor_id to vendor_name
vendor_mapping = {
    1: "Creative Mobile Technologies, LLC.",
    2: "VeriFone Inc."
}

# Create rate_code_id to rate_code_name
rate_code_mapping = {
    1: "standard Rate",
    2: "JFK",
    3: "Newark",
    4: "Nassau or Westchester",
    5: "Negotiated Fare",
    6: "Group Ride",
    99: "Unknown"
}

# Create store_and_fwd_flag to store_and_fwd_flag_name
store_and_fwd_mapping = {
    "Y": "store and Forward Trip",
    "N": "Not A Store and Forward Trip",
}

# Create payment_type to payment_type_name
payment_type_mapping = {
    1: "Credit Card",
    2: "Cash",
    3: "No Charge",
    4: "Dispute",
    5: "Unknown",
    6: "Voided Trip"
}

# Create trip_type to trip_type_name
trip_type_mapping = {
    1: "street-hail",
    2: "Dispatch"
}

# Create new columns based on dictionaries
trips["vendor_name"] = trips["vendor_id"].map(vendor_mapping).astype("category")
trips["rate_code_name"] = trips["rate_code_id"].map(rate_code_mapping).astype("category")
trips["store_and_fwd_flag_name"] = trips["store_and_fwd_flag"].map(store_and_fwd_mapping).astype("category")
trips["payment_type_name"] = trips["payment_type_id"].map(payment_type_mapping).astype("category")
trips["trip_type_name"] = trips["trip_type_id"].map(trip_type_mapping).astype("category")

trips.head()

real_zones = pd.read_csv("taxi_zones/taxi_zone_lookup.csv")

# Copy the DataFrame
zones = real_zones.copy()

zones.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 265 entries, 0 to 264
Data columns (total 4 columns):
 #   Column        Non-Null Count  Dtype 
---  ------        --------------  ----- 
 0   LocationID    265 non-null    int64 
 1   Borough       264 non-null    object
 2   Zone          264 non-null    object
 3   service_zone  263 non-null    object
dtypes: int64(1), object(3)
memory usage: 8.4+ KB

zones.duplicated().sum()

0

columns_to_check = [column for column in zones.columns if zones[column].dtype=="object"]

for column in columns_to_check:
    display(zones[zones[column].isnull()])

zones = zones.fillna("Unknown")
zones.isnull().sum()

LocationID      0
Borough         0
Zone            0
service_zone    0
dtype: int64

borough_mapping = dict(zip(zones["LocationID"], zones["Borough"]))
zone_mapping = dict(zip(zones["LocationID"], zones["Zone"]))
service_zone_mapping = dict(zip(zones["LocationID"], zones["service_zone"]))

trips["pickup_borough"] = trips["pickup_location_id"].map(borough_mapping).astype("category")
trips["pickup_zone"] = trips["pickup_location_id"].map(zone_mapping).astype("category")
trips["pickup_service_zone"] = trips["pickup_location_id"].map(service_zone_mapping).astype("category")

trips["dropoff_borough"] = trips["dropoff_location_id"].map(borough_mapping).astype("category")
trips["dropoff_zone"] = trips["dropoff_location_id"].map(zone_mapping).astype("category")
trips["dropoff_service_zone"] = trips["dropoff_location_id"].map(service_zone_mapping).astype("category")

trips_isnull_sum = trips.isnull().sum().reset_index().rename(columns={"index":"Column Name", 0:"Missing Value Counts"})
trips_isnull_sum = trips_isnull_sum[trips_isnull_sum["Missing Value Counts"]!=0]
trips_isnull_sum

import geopandas as gpd

# Load the shapefile
gdf = gpd.read_file("taxi_zones/taxi_zones.shp")

# Save as GeoJSON
gdf.to_file("taxi_zones/taxi_zones.geojson", driver="GeoJSON")

# Describing the cleaned data
trips.describe()

columns_to_remove = ["mta_tax", "improvement_surcharge"]
trips = trips.drop(columns=columns_to_remove)

import seaborn as sns
plt.subplot(1, 2, 1)
sns.boxplot(trips["trip_duration_minutes"])
plt.subplot(1, 2, 2)
sns.boxplot(trips["trip_duration_hours"])
plt.tight_layout()
plt.show()

q1 = trips["trip_duration_minutes"].quantile(0.25)
q3 = trips["trip_duration_minutes"].quantile(0.75)
iqr = q3 - q1
lower_bound = q1 - 1.5 * iqr
upper_bound = q3 + 1.5 * iqr
trips = trips[(trips["trip_duration_minutes"] >= lower_bound) & (trips["trip_duration_minutes"] <= upper_bound)]
print(trips.shape)
import seaborn as sns
plt.subplot(1, 2, 1)
sns.boxplot(trips["trip_duration_minutes"])
plt.subplot(1, 2, 2)
sns.boxplot(trips["trip_duration_hours"])
plt.tight_layout()
plt.show()

(42408, 33)

# Describing the cleaned data
trips.describe(include=["category","object"])

columns_to_remove = ["store_and_fwd_flag", "pickup_month", "store_and_fwd_flag_name"]
trips = trips.drop(columns=columns_to_remove)

real_trips.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 68211 entries, 0 to 68210
Data columns (total 20 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   VendorID               68211 non-null  int64  
 1   lpep_pickup_datetime   68211 non-null  object 
 2   lpep_dropoff_datetime  68211 non-null  object 
 3   store_and_fwd_flag     63887 non-null  object 
 4   RatecodeID             63887 non-null  float64
 5   PULocationID           68211 non-null  int64  
 6   DOLocationID           68211 non-null  int64  
 7   passenger_count        63887 non-null  float64
 8   trip_distance          68211 non-null  float64
 9   fare_amount            68211 non-null  float64
 10  extra                  68211 non-null  float64
 11  mta_tax                68211 non-null  float64
 12  tip_amount             68211 non-null  float64
 13  tolls_amount           68211 non-null  float64
 14  ehail_fee              0 non-null      float64
 15  improvement_surcharge  68211 non-null  float64
 16  total_amount           68211 non-null  float64
 17  payment_type           63887 non-null  float64
 18  trip_type              63877 non-null  float64
 19  congestion_surcharge   63887 non-null  float64
dtypes: float64(14), int64(3), object(3)
memory usage: 10.4+ MB

trips.info()

<class 'pandas.core.frame.DataFrame'>
Index: 42408 entries, 0 to 68010
Data columns (total 30 columns):
 #   Column                 Non-Null Count  Dtype         
---  ------                 --------------  -----         
 0   vendor_id              42408 non-null  category      
 1   pickup_datetime        42408 non-null  datetime64[ns]
 2   dropoff_datetime       42408 non-null  datetime64[ns]
 3   rate_code_id           42408 non-null  category      
 4   pickup_location_id     42408 non-null  category      
 5   dropoff_location_id    42408 non-null  category      
 6   passenger_count        42408 non-null  float64       
 7   trip_distance          42408 non-null  float64       
 8   fare_amount            42408 non-null  float64       
 9   extra                  42408 non-null  float64       
 10  tip_amount             42408 non-null  float64       
 11  tolls_amount           42408 non-null  float64       
 12  total_amount           42408 non-null  float64       
 13  payment_type_id        42408 non-null  category      
 14  trip_type_id           42408 non-null  category      
 15  congestion_surcharge   42408 non-null  float64       
 16  pickup_hour            42408 non-null  int32         
 17  pickup_day_of_week     42408 non-null  object        
 18  trip_duration_minutes  42408 non-null  float64       
 19  trip_duration_hours    42408 non-null  float64       
 20  vendor_name            42408 non-null  category      
 21  rate_code_name         42408 non-null  category      
 22  payment_type_name      42408 non-null  category      
 23  trip_type_name         42408 non-null  category      
 24  pickup_borough         42408 non-null  category      
 25  pickup_zone            42408 non-null  category      
 26  pickup_service_zone    42408 non-null  category      
 27  dropoff_borough        42408 non-null  category      
 28  dropoff_zone           42408 non-null  category      
 29  dropoff_service_zone   42408 non-null  category      
dtypes: category(16), datetime64[ns](2), float64(10), int32(1), object(1)
memory usage: 5.5+ MB

# Exporting data
trips = trips.sort_values("pickup_datetime")
trips.to_csv("NYC TLC Trip Record Cleaned.csv", index=False, sep=";")

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Create a new DataFrame with median tips for each hour and day of the week
median_tips_heatmap = trips.groupby(["pickup_day_of_week", "pickup_hour"])["tip_amount"].median().unstack()

# Define the order of days and hours
day_of_week = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
hours_of_day = range(24)  # Assuming 24 hours

# Reindex to ensure all days and hours are present
median_tips_heatmap = median_tips_heatmap.reindex(index=day_of_week, columns=hours_of_day)

# Create the heatmap
plt.figure(figsize=(12, 8))
sns.heatmap(median_tips_heatmap, cmap="Blues", annot=False, cbar_kws={"label": "Median Tip Amount ($)"})
plt.title("Median Tip Amounts by Hour of the Day and Day of the Week")
plt.xlabel("Hour of the Day")
plt.ylabel("Day of the Week")
plt.xticks(rotation=45)
plt.yticks(rotation=0)
plt.show()

import pandas as pd
from scipy.stats import kruskal

# Prepare data for the Kruskal-Wallis test
# Create a list to hold the tip amounts for each combination of day and hour
tips_by_hour_and_day = []

# Loop through each day of the week and each hour of the day
for day in ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]:
    for hour in range(24):
        # Get the tip amounts for the specific day and hour
        tips = trips[(trips["pickup_day_of_week"] == day) & (trips["pickup_hour"] == hour)]["tip_amount"]
        tips_by_hour_and_day.append(tips)

# Perform the Kruskal-Wallis test
# Filter out empty series to avoid errors
filtered_tips = [tips for tips in tips_by_hour_and_day if not tips.empty]

if len(filtered_tips) > 1:  # Ensure there are at least two groups to compare
    stat, p = kruskal(*filtered_tips)  # Unpack the list directly into the function
    print(f"Statistic: {stat:.2f}, p={p:.2f}")

    # Set significance level
    alpha = 0.05

    # Interpret the results
    if p <= alpha:
        print("P-value is less than or equal to alpha. There is enough evidence to reject the null hypothesis. The median tip amounts are not equal across all days and hours.")
    else:
        print("P-value is greater than alpha. There is not enough evidence to accept the null hypothesis. The median tip amounts are equal across all days and hours.")
else:
    print("Not enough data to perform the Kruskal-Wallis test.")

Statistic: 634.71, p=0.00
P-value is less than or equal to alpha. There is enough evidence to reject the null hypothesis. The median tip amounts are not equal across all days and hours.

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np

median_tips = trips.groupby("payment_type_name")["tip_amount"].median().reset_index().iloc[:2]
# Create barplot
plt.figure(figsize=(10, 6))
sns.barplot(
    x="payment_type_name", 
    y="tip_amount", 
    data=median_tips,
    order=median_tips.sort_values("tip_amount", ascending=False)["payment_type_name"]  # Sort by highest median
)

# Add titles and labels
plt.title("Median Tip Amount by Payment Type", fontsize=14, pad=20)
plt.xlabel("Payment Type", fontsize=12)
plt.ylabel("Median Tip Amount ($)", fontsize=12)

# Rotate x-labels if needed
plt.xticks(rotation=45, ha='right')

# Display the plot
plt.tight_layout()  # Prevents label cutoff
plt.show()

from scipy.stats import mannwhitneyu
# The Mann-Whitney U test is performed to compare tip amounts
cash_tips = trips[trips["payment_type_name"] == "Cash"]["tip_amount"]
credit_tips = trips[trips["payment_type_name"] == "Credit Card"]["tip_amount"]
stat, p = mannwhitneyu(credit_tips, cash_tips, alternative="two-sided")
alpha = 0.05

print(f"P-value: {p}")
if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal between cash and credit card payment methods.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. The median tip amounts are equal between cash and credit card payment methods.")

P-value: 0.0
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal between cash and credit card payment methods.

import folium

# Load the GeoJSON data
gdf = gpd.read_file("taxi_zones/taxi_zones.geojson")

# Calculate median tips by borough
median_tips = trips.groupby("pickup_borough")["tip_amount"].median().reset_index()

# Merge the median tips with the GeoDataFrame
gdf = gdf.merge(median_tips, left_on="borough", right_on="pickup_borough")

# Create the map
m = folium.Map(location=[40.7128, -74.0060], zoom_start=10)

# Add choropleth layer
choropleth = folium.Choropleth(
    geo_data=gdf,
    data=median_tips,
    columns=["pickup_borough", "tip_amount"],
    key_on="feature.properties.borough",
    fill_color="Blues",
    fill_opacity=0.7,
    line_opacity=0.2,
    legend_name="Median Tip Amount ($)"
).add_to(m)

# Add tooltips
folium.features.GeoJsonTooltip(
    fields=["borough", "tip_amount"],
    aliases=["Borough: ", "Median Tip: $"],
    style=("background-color: white; color: #333333; font-family: arial; font-size: 12px;")
).add_to(choropleth.geojson)

# Display the map
m

from scipy.stats import kruskal

# Prepare data for the Kruskal-Wallis test
manhattan_tips = trips[trips["pickup_borough"] == "Manhattan"]["tip_amount"]
brooklyn_tips = trips[trips["pickup_borough"] == "Brooklyn"]["tip_amount"]
queens_tips = trips[trips["pickup_borough"] == "Queens"]["tip_amount"]
bronx_tips = trips[trips["pickup_borough"] == "Bronx"]["tip_amount"]
staten_island_tips = trips[trips["pickup_borough"] == "Staten Island"]["tip_amount"]

# Perform the Kruskal-Wallis test
stat, p = kruskal(manhattan_tips, brooklyn_tips, queens_tips, bronx_tips, staten_island_tips)
print(f"Statistic: {stat:.2f}, p={p:.2f}")
alpha = 0.05

if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all boroughs.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. The median tip amounts are equal across all boroughs")

Statistic: 2251.39, p=0.00
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all boroughs.

import folium

# Load the GeoJSON data
gdf = gpd.read_file("taxi_zones/taxi_zones.geojson")

# Calculate median tips by borough
median_tips = trips.groupby("dropoff_borough")["tip_amount"].median().reset_index()

# Merge the median tips with the GeoDataFrame
gdf = gdf.merge(median_tips, left_on="borough", right_on="dropoff_borough")

# Create the map
m = folium.Map(location=[40.7128, -74.0060], zoom_start=10)

# Add choropleth layer
choropleth = folium.Choropleth(
    geo_data=gdf,
    data=median_tips,
    columns=["dropoff_borough", "tip_amount"],
    key_on="feature.properties.borough",
    fill_color="Blues",
    fill_opacity=0.7,
    line_opacity=0.2,
    legend_name="Median Tip Amount ($)"
).add_to(m)

# Add tooltips
folium.features.GeoJsonTooltip(
    fields=["borough", "tip_amount"],
    aliases=["Borough: ", "Median Tip: $"],
    style=("background-color: white; color: #333333; font-family: arial; font-size: 12px;")
).add_to(choropleth.geojson)

# Display the map
m

from scipy.stats import kruskal

# Prepare data for the Kruskal-Wallis test
manhattan_tips = trips[trips["dropoff_borough"] == "Manhattan"]["tip_amount"]
brooklyn_tips = trips[trips["dropoff_borough"] == "Brooklyn"]["tip_amount"]
queens_tips = trips[trips["dropoff_borough"] == "Queens"]["tip_amount"]
bronx_tips = trips[trips["dropoff_borough"] == "Bronx"]["tip_amount"]
staten_island_tips = trips[trips["dropoff_borough"] == "Staten Island"]["tip_amount"]

# Perform the Kruskal-Wallis test
stat, p = kruskal(manhattan_tips, brooklyn_tips, queens_tips, bronx_tips, staten_island_tips)
print(f"Statistic: {stat:.2f}, p={p:.2f}")
alpha = 0.05

if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all boroughs.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. The median tip amounts are equal across all boroughs")

Statistic: 2562.29, p=0.00
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all boroughs.

median_tips = trips.groupby("pickup_service_zone")["tip_amount"].median().reset_index()
plt.figure(figsize=(10, 6))
service_zones = ["Airports", "Boro Zone", "Yellow Zone"]
sns.barplot(x="pickup_service_zone", y="tip_amount", data=median_tips, order=service_zones)
plt.title("Median Tip Amounts by Pickup Service Zone")
plt.ylabel("Median Tip Amount ($)")
plt.xlabel("Pickup Service Zone")
plt.xticks(rotation=45)
plt.show()

from scipy.stats import kruskal

# Prepare data for the Kruskal-Wallis test
airport_tips = trips[trips["pickup_service_zone"] == "Airports"]["tip_amount"]
boro_tips = trips[trips["pickup_service_zone"] == "Boro Zone"]["tip_amount"]
yellow_tips = trips[trips["pickup_service_zone"] == "Yellow Zone"]["tip_amount"]
# Perform the Kruskal-Wallis test
stat, p = kruskal(airport_tips, boro_tips, yellow_tips)
print(f"Statistic: {stat:.2f}, p={p:.2f}")
alpha = 0.05

if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all service zones.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. The median tip amounts are equal across all service zones.")

Statistic: 505.02, p=0.00
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all service zones.

median_tips = trips.groupby("dropoff_service_zone")["tip_amount"].median().reset_index()
plt.figure(figsize=(10, 6))
service_zones = ["Airports", "Boro Zone", "Yellow Zone"]
sns.barplot(x="dropoff_service_zone", y="tip_amount", data=median_tips, order=service_zones)
plt.title("Median Tip Amounts by Dropoff Service Zone")
plt.ylabel("Median Tip Amount ($)")
plt.xlabel("Dropoff Service Zone")
plt.xticks(rotation=45)
plt.show()

from scipy.stats import kruskal

# Prepare data for the Kruskal-Wallis test
airport_tips = trips[trips["dropoff_service_zone"] == "Airports"]["tip_amount"]
boro_tips = trips[trips["dropoff_service_zone"] == "Boro Zone"]["tip_amount"]
yellow_tips = trips[trips["dropoff_service_zone"] == "Yellow Zone"]["tip_amount"]

# Perform the Kruskal-Wallis test
stat, p = kruskal(airport_tips, boro_tips, yellow_tips)
print(f"Statistic: {stat:.2f}, p={p:.2f}")
alpha = 0.05

if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all service zones.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. The median tip amounts are equal across all service zones.")

Statistic: 4912.93, p=0.00
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. The median tip amounts are not equal across all service zones.

plt.figure(figsize=(10, 6))
sns.scatterplot(data=trips, x="trip_distance", y="tip_amount", alpha=0.1)
sns.regplot(data=trips, x="trip_distance", y="tip_amount", scatter=False, color="red")
plt.xlabel("Trip Distance (miles)")
plt.ylabel("Tip Amount ($)")
plt.title("Trip Distance vs. Tip Amount")
plt.show()

# Spearman"s correlation is calculated
from scipy.stats import spearmanr
corr, p = spearmanr(trips["trip_distance"], trips["tip_amount"])

alpha = 0.05

print(f"Correlation: {corr:.2f}, P-value: {p}")
if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the trip distances and tip amount.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. There is monotonic relationship between the trip distances and tip amount.")

Correlation: 0.27, P-value: 0.0
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the trip distances and tip amount.

plt.figure(figsize=(10,6))
sns.scatterplot(data=trips, x="trip_duration_minutes", y="tip_amount", alpha=0.1)
sns.regplot(data=trips, x="trip_duration_minutes", y="tip_amount", scatter=False, color="red")
plt.xlabel("Trip Duration (minutes)")
plt.ylabel("Tip Amount ($)")
plt.title("Trip Durations vs. Tip Amount")
plt.show()

# Spearman"s correlation is calculated
from scipy.stats import spearmanr
corr, p = spearmanr(trips["trip_duration_minutes"], trips["tip_amount"])

alpha = 0.05

print(f"Correlation: {corr:.2f}, P-value: {p}")
if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the trip durations and tip amount.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. There is monotonic relationship between the trip durations and tip amount.")

Correlation: 0.24, P-value: 0.0
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the trip durations and tip amount.

plt.figure(figsize=(10,6))
sns.scatterplot(data=trips, x="extra", y="tip_amount", alpha=0.1)
sns.regplot(data=trips, x="extra", y="tip_amount", scatter=False, color="red")
plt.xlabel("Extra Charges")
plt.ylabel("Tip Amount ($)")
plt.title("Extra Charges vs. Tip Amount")
plt.show()

# Spearman"s correlation is calculated
from scipy.stats import spearmanr
corr, p = spearmanr(trips["extra"], trips["tip_amount"])

alpha = 0.05

print(f"Correlation: {corr:.2f}, P-value: {p:.2f}")
if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the extra charges and tip amount.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. There is monotonic relationship between the extra charges and tip amount.")

Correlation: 0.06, P-value: 0.00
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the extra charges and tip amount.

plt.figure(figsize=(10,6))
sns.scatterplot(data=trips, x="tolls_amount", y="tip_amount", alpha=0.1)
sns.regplot(data=trips, x="tolls_amount", y="tip_amount", scatter=False, color="red")
plt.xlabel("Tolls Amount")
plt.ylabel("Tip Amount ($)")
plt.title("Tolls Amount vs. Tip Amount")
plt.show()

from scipy.stats import spearmanr
corr, p = spearmanr(trips["tolls_amount"], trips["tip_amount"])

alpha = 0.05

print(f"Correlation: {corr:.2f}, P-value: {p:.2f}")
if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the toll amounts and tip amounts.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. There is a monotonic relationship between the toll amounts and tip amounts.")

Correlation: 0.00, P-value: 0.85
P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. There is a monotonic relationship between the toll amounts and tip amounts.

plt.figure(figsize=(10,6))
sns.scatterplot(data=trips, x="congestion_surcharge", y="tip_amount", alpha=0.1)
sns.regplot(data=trips, x="congestion_surcharge", y="tip_amount", scatter=False, color="red")
plt.xlabel("Congestion Surcharges")
plt.ylabel("Tip Amount ($)")
plt.title("Congestion Surcharges vs. Tip Amount")
plt.show()

from scipy.stats import spearmanr
corr, p = spearmanr(trips["congestion_surcharge"], trips["tip_amount"])

alpha = 0.05

print(f"Correlation: {corr:.2f}, P-value: {p:.2f}")
if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the congestion surcharges and tip amounts.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. There is a monotonic relationship between the congestion surcharges and tip amounts.")

Correlation: 0.35, P-value: 0.00
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the congestion surcharges and tip amounts.

plt.figure(figsize=(10,6))
sns.scatterplot(data=trips, x="passenger_count", y="tip_amount", alpha=0.1)
sns.regplot(data=trips, x="passenger_count", y="tip_amount", scatter=False, color="red")
plt.xlabel("Passengers")
plt.ylabel("Tip Amount ($)")
plt.title("Passengers count vs. Tip Amount")
plt.show()

from scipy.stats import spearmanr
corr, p = spearmanr(trips["passenger_count"], trips["tip_amount"])

alpha = 0.05

print(f"Correlation: {corr:.2f}, P-value: {p:.2f}")
if p <= alpha:
    print("P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the passengers count and tip amounts.")
else:
    print("P-value is greater than alpha. There are not enough evidences to accept the null hypothesis. There is a monotonic relationship between the passengers count and tip amounts.")

Correlation: 0.01, P-value: 0.01
P-value is less than or equal to alpha. There are enough evidences to reject the null hypothesis. There is no monotonic relationship between the passengers count and tip amounts.

	vendor_id	pickup_datetime	dropoff_datetime	store_and_fwd_flag	rate_code_id	pickup_location_id	dropoff_location_id	passenger_count	trip_distance	fare_amount	extra	mta_tax	tip_amount	improvement_surcharge	total_amount	payment_type_id	trip_type_id	congestion_surcharge	pickup_day_of_week	pickup_month	trip_duration_minutes	trip_duration_hours
0	2	2023-01-01 00:26:10	2023-01-01 00:37:11	N	1	166	143	1.00	2.58	14.90	1.00	0.50	4.03	1.00	24.18	1	1	2.75	Sunday	January	11.02	0.18
1	2	2023-01-01 00:51:03	2023-01-01 00:57:49	N	1	24	43	1.00	1.81	10.70	1.00	0.50	2.64	1.00	15.84	1	1	0.00	Sunday	January	6.77	0.11
2	2	2023-01-01 00:35:12	2023-01-01 00:41:32	N	1	223	179	1.00	0.00	7.20	1.00	0.50	1.94	1.00	11.64	1	1	0.00	Sunday	January	6.33	0.11
5	2	2023-01-01 00:53:31	2023-01-01 01:11:04	N	1	41	262	1.00	2.78	17.70	1.00	0.50	0.00	1.00	22.95	2	1	2.75	Sunday	January	17.55	0.29
7	2	2023-01-01 00:11:58	2023-01-01 00:24:55	N	1	24	75	1.00	1.88	14.20	1.00	0.50	0.00	1.00	16.70	2	1	0.00	Sunday	January	12.95	0.22

	pickup_datetime	dropoff_datetime	passenger_count	trip_distance	fare_amount	extra	mta_tax	tip_amount	tolls_amount	improvement_surcharge	total_amount	congestion_surcharge	pickup_hour	trip_duration_minutes	trip_duration_hours
count	43484	43484	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00	43484.00
mean	2023-01-16 22:31:53.118687232	2023-01-16 22:47:33.103670016	1.36	1.93	12.90	0.75	0.50	1.70	0.02	1.00	17.55	0.68	14.12	15.67	0.26
min	2023-01-01 00:01:31	2023-01-01 00:16:02	1.00	0.00	0.00	0.00	0.50	0.00	0.00	1.00	1.50	0.00	0.00	0.00	0.00
25%	2023-01-09 12:15:41.249999872	2023-01-09 12:31:58.750000128	1.00	1.09	8.60	0.00	0.50	0.00	0.00	1.00	12.20	0.00	11.00	6.77	0.11
50%	2023-01-17 09:13:36	2023-01-17 09:24:28.500000	1.00	1.66	12.10	0.00	0.50	1.46	0.00	1.00	16.32	0.00	15.00	10.17	0.17
75%	2023-01-24 15:51:52.500000	2023-01-24 16:05:54.750000128	1.00	2.52	16.30	1.00	0.50	3.00	0.00	1.00	21.95	0.00	18.00	14.45	0.24
max	2023-01-31 23:58:23	2023-02-01 17:27:05	6.00	6.35	31.00	2.50	0.50	7.64	11.75	1.00	37.20	2.75	23.00	1438.93	23.98
std	NaN	NaN	1.06	1.20	5.60	1.01	0.00	1.82	0.34	0.00	6.94	1.19	5.19	76.73	1.28

	Column Name	Type	sample Value
0	VendorID	int64	2
1	lpep_pickup_datetime	object	2023-01-01 00:26:10
2	lpep_dropoff_datetime	object	2023-01-01 00:37:11
3	store_and_fwd_flag	object	N
4	RatecodeID	float64	1.00
5	PULocationID	int64	166
6	DOLocationID	int64	143
7	passenger_count	float64	1.00
8	trip_distance	float64	2.58
9	fare_amount	float64	14.90
10	extra	float64	1.00
11	mta_tax	float64	0.50
12	tip_amount	float64	4.03
13	tolls_amount	float64	0.00
14	ehail_fee	float64	NaN
15	improvement_surcharge	float64	1.00
16	total_amount	float64	24.18
17	payment_type	float64	1.00
18	trip_type	float64	1.00
19	congestion_surcharge	float64	2.75

	Column Name	Missing Value Counts	Percentage (%)
3	store_and_fwd_flag	4324	6.34
4	RatecodeID	4324	6.34
7	passenger_count	4324	6.34
14	ehail_fee	68211	100.00
17	payment_type	4324	6.34
18	trip_type	4334	6.35
19	congestion_surcharge	4324	6.34

	vendor_id	store_and_fwd_flag	rate_code_id	pickup_location_id	dropoff_location_id	payment_type_id	trip_type_id	pickup_day_of_week	pickup_month	vendor_name	rate_code_name	store_and_fwd_flag_name	payment_type_name	trip_type_name	pickup_borough	pickup_zone	pickup_service_zone	dropoff_borough	dropoff_zone	dropoff_service_zone
count	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408	42408
unique	2	1	3	159	230	4	2	7	1	2	3	1	4	2	6	158	4	6	229	4
top	2	N	1	74	74	1	1	Tuesday	January	VeriFone Inc.	standard Rate	Not A Store and Forward Trip	Credit Card	street-hail	Manhattan	East Harlem North	Boro Zone	Manhattan	East Harlem North	Boro Zone
freq	42309	42408	42384	9302	2559	26414	42407	6959	42408	42309	42384	42408	26414	42407	26292	9302	39499	26146	2559	28829

NYC TLC Taxi Trip Data Analysis: Tipping Behavior¶

Background¶

Gap Analysis¶

Problem Statement¶

Insight Questions¶

Data Understanding¶

Data Wrangling¶

The Data Wrangling Process¶

Discovering¶

Structuring¶

Cleaning¶

Enriching¶

Validating¶

Exploratory Data Analysis (EDA)¶

How do the tipping patterns vary by hour of the day and day of the week?¶

Insights¶

Recommendations¶

How does payment method (Cash vs. Credit Card) affect tipping behavior in NYC taxis?¶

Insights¶

Recommendations¶

How does the pickup borough influence tipping?¶

Insights¶

Recommendations¶

How does the dropoff borough influence tipping?¶

Insights¶

Recommendations¶

How does the pickup service zone influence tipping?¶

Insights¶

Recommendations¶

How does the dropoff service zone influence tipping?¶

Insights¶

Recommendations¶

What is the relationship between trip distances and tip amount?¶

Insights¶

Recommendations¶

What is the relationship between trip durations and tip amount?¶

Insights¶

Recommendations¶

What is the relationship between extra charges and tip amount?¶

Insights¶

Recommendations¶

What is the relationship between tolls amount on tip amounts?¶

Insights¶

Recommendations¶

What is the relationship between congestion surcharges on tip amounts?¶

Insights¶

Recommendations¶

What is the relationship between passengers count on tip amounts?¶

Insights¶

Recommendations¶

Conclusions¶

References¶